# coding: utf-8

# 修复重复的七陌通话记录

import os
import json
import time
import datetime

import set_env_path     # 请勿删除
from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def fix_duplicate_seven_moor_record():
    """
    add by zhouhl 2018-06-07
    """
    find_duplicate_call_sheet_id_sql = text("""
    select
        call_sheet_id, count(*) as count_num
    from
        seven_moor_records
    group by
        call_sheet_id
    having
        count_num > 1
    order by count_num desc;
    """)

    find_error_record_sql = text("""
    select
        *
    from
        seven_moor_records
    where
        call_sheet_id=:call_sheet_id
    order by
        id desc;
    """)

    update_sql = text("""
    update
        seven_moor_records
    set
        transform_result=:transform_result,
        handle_time=:handle_time,
        demand_id=:demand_id
    where
        id=:id;
    """)

    delete_sql = text("""
    delete from
        seven_moor_records
    where
        call_sheet_id=:call_sheet_id
        and id <>:id;
    """)

    all_call_sheet_ids = conn.execute(
        find_duplicate_call_sheet_id_sql
    ).fetchall()
    for i in all_call_sheet_ids:
        call_sheet_id = i.call_sheet_id
        error_records = conn.execute(
            find_error_record_sql,
            call_sheet_id=call_sheet_id,
        ).fetchall()
        transform_result = None
        handle_time = None
        demand_id = None
        for j in error_records:
            if not transform_result and not handle_time and not demand_id:
                transform_result = j.transform_result
                handle_time = j.handle_time
                demand_id = j.demand_id
        else:
            valid_id = j.id

        conn.execute(
            update_sql,
            transform_result=transform_result,
            handle_time=handle_time,
            demand_id=demand_id,
            id=valid_id,
        )

        conn.execute(
            delete_sql,
            call_sheet_id=call_sheet_id,
            id=valid_id,
        )


if __name__ == '__main__':
    fix_duplicate_seven_moor_record()
